﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;

public class ExcelUtil
{
    private static Dictionary<string, Dictionary<string, string>> LoadXlsFile(string filePath)
    {
        var result = new Dictionary<string, Dictionary<string, string>>();
        filePath = filePath.Replace("\\", "/");
        FileStream fs = File.OpenRead(filePath);
        try
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(filePath);
            if (extension.Equals(".xls"))
            {
                //把xls文件中的数据写入wk中
                wk = new HSSFWorkbook(fs);

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                //第二行是key字段
                IRow row_key = sheet.GetRow(1);

                for (int i = 3; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);

                    // 以ID为key值，创建一个新的集合，用于保存当前行的数据
                    string id = row.Cells[0].ToString().Trim();
                    result[id] = new Dictionary<string, string>();

                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        // 每一行的数据存储规则：Key字段-Value值
                        string key = row_key.Cells[j].ToString().Trim();
                        string value = row.Cells[j].ToString().Trim();
                        result[id][key] = value;
                    }

                }

            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            fs.Close();
        }

        return result;
    }

    /// <summary>
    /// 加载CSV结构数据
    /// </summary>
    /// <typeparam name="T_CsvData"></typeparam>
    /// <param name="csvFilePath"></param>
    /// <returns></returns>
    public static Dictionary<int, T_CsvData> LoadCsvData<T_CsvData>(string csvFilePath)
    {
        var dict = new Dictionary<int, T_CsvData>();
        Dictionary<string, Dictionary<string, string>> result = LoadXlsFile(csvFilePath);

        // 遍历每一行数据
        foreach (string id in result.Keys)
        {
            // CSV的一行数据
            Dictionary<string, string> datas = result[id];

            // 读取Csv数据对象的属性
            PropertyInfo[] props = typeof(T_CsvData).GetProperties();

            // 使用反射，将CSV文件的数据赋值给CSV数据对象的相应字段，要求CSV文件的字段名和CSV数据对象的字段名完全相同
            T_CsvData obj = System.Activator.CreateInstance<T_CsvData>();

            foreach (PropertyInfo pi in props)
            {
                pi.SetValue(obj, System.Convert.ChangeType(datas[pi.Name], pi.PropertyType), null);
            }

            // 按ID-数据的形式存储
            dict[System.Convert.ToInt32(id)] = obj;
        }

        return dict;
    }

    /// <summary>
    /// xls文件-》C#类文件
    /// </summary>
    /// <param name="src_filePath"></param>
    /// <param name="dst_filepath"></param>
    public static void CsvFileToCSharpClass(string src_filePath, string dst_filepath = "")
    {
        string filePath = src_filePath.Replace("\\", "/");
        FileStream fs = File.OpenRead(filePath);
        try
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(filePath);
            if (extension.Equals(".xls"))
            {
                //把xls文件中的数据写入wk中
                wk = new HSSFWorkbook(fs);

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                //第一行是类型字段
                IRow row_type = sheet.GetRow(0);
                //第二行是属性字段
                IRow row_property = sheet.GetRow(1);


                //类的名字
                string className = Path.GetFileNameWithoutExtension(src_filePath);
                //类的结构
                string code = "using System.Collections;\nusing System.Collections.Generic;\n\n";
                code += String.Format("////////////////////////////////\n/// {0}.cs——{1}信息类\n////////////////////////////////\n", className, className);
                code += String.Format("{0}public class {1} {{ \n\n", "", className);

                for (int i = 0; i < row_type.Cells.Count; i++)
                {
                    string typeName = row_type.Cells[i].ToString().Trim();
                    string variableName = row_property.Cells[i].ToString().Trim();
                    code += String.Format("{0}public {1} {2} {{ get; set; }}\n\n", "\t", typeName, variableName);
                }

                code += "}\n";

                src_filePath = src_filePath.Replace("\\", "/");
                string folder = src_filePath.Substring(0, src_filePath.LastIndexOf("/") + 1);
                string CSharpFile = "";
                if (string.IsNullOrEmpty(dst_filepath))
                {
                    CSharpFile = String.Format("{0}{1}.cs", folder, className);
                }
                else
                {
                    CSharpFile = dst_filepath;
                }
                Console.WriteLine(CSharpFile);

                File.WriteAllText(CSharpFile, code);

            }
        }
        catch
        {
            fs.Close();
        }


    }

}
